package com.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.dao.UserDao;
import com.po.User;
import com.utils.DataSourceUtils;

public class UserDaoImpl implements UserDao {

	public UserDaoImpl() {
		// TODO Auto-generated constructor stub
	}

	@Override
	public User findById(String id) throws SQLException {
		QueryRunner qr = new  QueryRunner(DataSourceUtils.getDataSource());
		String sql="select * from user where uid = ?";
		return qr.query(sql, new BeanHandler(User.class), id);
	}

	@Override
	public List<User> findAll() throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="select * from user";
		return qr.query(sql, new BeanListHandler<User>(User.class));
	}

	@Override
	public boolean save(User u) throws SQLException {
		boolean b = false;
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="insert into user values(?,?,?,?,?,?,?,?,?,?,?,?) ";
		int i = qr.update(sql, u.getUid(),u.getUname(),u.getUpwd(),u.getUtype(),u.getUtname(),
				u.getUbirthday(),u.getUphone(),u.getUemail(),
				u.getUcomment(),u.getUvote(),u.getUup());
		if (i==1) {
			b = true;
		}
		return b;
	}

	@Override
	public boolean update(User u) throws SQLException {
		boolean b = false;
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "update user set upwd = ?,utname = ?,uphone = ?,uemail = ?,ubirthday = ? where uid = ?";
		int i = qr.update(sql,u.getUpwd(),u.getUtname(),u.getUphone(),u.getUemail(),u.getUbirthday(),u.getUid());
		if (i==1){
			b = true;
		}
		return b;
	}
	public boolean updateUup(String uid ,Byte uup) throws SQLException {
		boolean b = false;
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "update user set uup = ? where uid = ?";
		int i = qr.update(sql,uup,uid);
		if (i==1){
			b = true;
		}
		return b;
	}
	
	public boolean updateUpwd(String uid ,String upwd) throws SQLException {
		boolean b = false;
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "update user set upwd = ? where uid = ?";
		int i = qr.update(sql,upwd,uid);
		if (i==1){
			b = true;
		}
		return b;
	}

	@Override
	public boolean delete(User u) throws SQLException {
		boolean b = false;
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="delete from user where uid = ?";
		int i = qr.update(sql, u.getUid());
		if (i==1) {
			b = true;
		}
		return b;
	}

	@Override
	public User findByName(String username) throws SQLException {
		QueryRunner qr = new  QueryRunner(DataSourceUtils.getDataSource());
		String sql="select * from user where uname = ?";
		return qr.query(sql, new BeanHandler(User.class), username);
	}

	public static void main(String[] args) {
		UserDaoImpl u = new UserDaoImpl();
		User uu = null;
		try {
			uu = u.findByName("1");
			System.out.println(uu.getUid());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	@Override
	public List<User> findAllByPage(int currPage, int pageSize)
			throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="select * from user limit ?,?";
		return qr.query(sql, new BeanListHandler<User>(User.class), (currPage-1)*pageSize,pageSize);
	}

	@Override
	
	/**
	 * 多条件查询
	 * @param name 商品名称
	 * @param kw 关键词
	 * @return list
	 * @throws SQLException 
	 */
	public List<User> findByCondition(String name, String kw,int currPage, int pageSize)
			throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="select * from user where "+name+"=? limit ?,?";	
		return qr.query(sql, new BeanListHandler<User>(User.class), kw,(currPage-1)*pageSize,pageSize);
	}
	
	public List<User> findByConditionAndLike(String name, String kw,int currPage, int pageSize)
			throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="select * from user where "+name+" like ? limit ?,?";	
		return qr.query(sql, new BeanListHandler<User>(User.class), "%"+kw+"%",(currPage-1)*pageSize,pageSize);
	}
	
	@Override
	public int getCount() throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="select count(*) from user";
		return ((Long)qr.query(sql, new ScalarHandler())).intValue();
	}
	
	public int getCountByCondition(String name ,String kw) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="select count(*) from user where "+name+"=?";
		return ((Long)qr.query(sql, new ScalarHandler(),kw)).intValue();
	}
	
	public int getCountByConditionAndLike(String name ,String kw) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="select count(*) from user where "+name+" like ?";
		return ((Long)qr.query(sql, new ScalarHandler(),"%"+kw+"%")).intValue();
	}

	@Override
	public User findByType(String type) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	public boolean updateUcommand(String uid, Byte ucomment) throws SQLException {
		boolean b = false;
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "update user set ucomment = ? where uid = ?";
		int i = qr.update(sql,ucomment,uid);
		if (i==1){
			b = true;
		}
		return b;
	}

	public boolean updateUvote(String uvote, Byte bb) throws SQLException {
		// TODO Auto-generated method stub
		boolean b = false;
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "update user set uvote = ? where uid = ?";
		int i = qr.update(sql,bb,uvote);
		if (i==1){
			b = true;
		}
		return b;
	}

	
}
